Field names must start with a letter, followed by any number of digits or letters.
VALUES_WITH_TAX = 47.50 |
Field name may include the following special letters:
For any other special letters, such as letter £ (pound), field names be be encapsulated in double quotes.
"VALUES_IN_£" = 47.50 |
Strings are automatically casted into Number, Date, Time and Timestamp values.
ISO | YYYY-MM-DD |
USA | MM/DD/YYYY |
EUR | DD.MM.YYYY |
ISO | HH24.MI.SS |
USA (am) | HH12:MI AM |
USA (pm) | HH12:MI PM |
EUR | HH24:MI:SS |
ISO | YYYY-MM-DD HH24:MI:SS.NNN |
IBM | YYYY-MM-DD-HH24.MI.SS.NNN |
Function | Example |
---|---|
Abs | Abs(7.59) = 7.59 Abs(-7.59) = 7.59 |
Add | 7.59 + 2.41 = 10.0 Date('2016-02-27') + Day(5) = '2016-03-03' Time('15.10.30') + Hour(15) = '06:10:30' Timestamp('2018-12-21-15.10.30.123') + Hour(5) = '2018-12-21-20.10.30.123' |
Between | 5 Between 1 And 10 Date('2018-12-21') Between '2018-12-20' And '2018-12-22' Time('15.10.30') Between '15.10.29' And '15.10.31' Timestamp('2018-12-21-15.10.30') Between '2018-12-21-15.10.29' And '2018-12-21-15.10.31' |
Ceil | Ceil(10.5) = 11 Ceil(1.234e2) = 124 |
Concat | Concat('Hello', '-World') = 'Hello-World' 'a' || 'b' || 'c' = 'abc' |
Date | Date('2018-12-05') = '2018-12-05' Date('2018-12-05') > '2018-12-04' Date(Timestamp('2018-12-05-15.30.00.123')) = '2018-12-05' |
Day | Day(Date('2018-12-05')) = 5 Date('2018-12-05') + Day(10) = '2018-12-15' Date('2018-12-05') - Day(10) = '2018-11-25' |
Divide | 26 / 8 = 3.25 26 / '3.25' = 8 |
Floor | Floor(10.5) = 10 Floor(1.234e2) = 123 |
Hour | Hour(Time('12:00:00')) = 12 Time('12:00:00') + Hour(3) = '15:00:00' Time('12:00:00') - Hour(3) = '09:00:00' |
In | 'PT' In('PT', 'PX', 'UB', 'UP', 'UR', 'DL', 'DR') '2.2' In(1, 2.2, 3, 4.5, 5, 6) Date('2018-12-20') In(Date('2018-12-19'), '2018-12-20', '2018-12-21') |
Length | Length('Hello World') = 11 |
Like | 'Westend New York' Like('%New%') = true 'Westend New York' Like('__stend New York') = true 'Eastend New York' Like('__stend New York') = true
With regular expression (expression starts and ends with a slash): |
Lower | Lower('Hello World') = 'hello world' |
Lpad | Lpad('Hello', 10) = ' Hello' Lpad('Hello', 10, '-+') = '-+-+-Hello' |
Ltrim | LTrim(' It works! ') = 'It works! ' LTrim('***It works!###', '*') = 'It works!###' LTrim('123It works!789', '0123456789') = 'It works!789' |
Microsecond | Microsecond(Timestamp('2018-12-05-15.00.55.123')) = 123 |
Minute | Minute(Time('12:25:00')) = 25 Time('12:00:00') + Minute(65) = '13:05:00' Time('12:00:00') - Minute(65) = '10:55:00' |
Modulus | 11 Mod 3 = 2 9 Mod 3 = 0 |
Month | Month(Date('2018-12-05')) = 12 Date('2018-12-05') + Month(1) = '2019-01-05' Date('2018-12-05') - Month(1) = '2018-11-05' |
Multiply | 3.25 * 8 = 26 8 * '3.25' = 26 |
Regexp_Like | 'Westend New York' Regexp_Like('.*New.*') = true 'Westend New York' Regexp_Like('.{2}stend New York') = true 'Eastend New York' Regexp_Like('.{2}stend New York') = true |
Replace | Replace('ZZZ-ABC-ZZZ-ABC-ZZZ', 'ABC', 'WWW') = 'ZZZ-WWW-ZZZ-WWW-ZZZ' Replace('ZZZ-ABC-ZZZ-ABC-ZZZ', 'ABC') = 'ZZZ--ZZZ--ZZZ' |
Round | Round(26.4) = 26 Round(26.5) = 27
Round(125.432, 1) = 125.4
Round(125.432, 0) = 125
Round(125.432, -1) = 130
Round(Date('2018-07-01'), 'YYYY') = '2019-01-01'
Round(Time('23:30:00'), 'HH24') = '00:00:00' |
Rpad | Rpad('Hello', 10) = 'Hello ' Rpad('Hello', 10, '-+') = 'Hello-+-+-' |
Rtrim | RTrim(' It works! ') = ' It works!' RTrim('***It works!###', '#') = '***It works!' RTrim('123It works!789', '0123456789') = '123It works!' |
Second | Second(Time('12:00:25')) = 25 Time('12:00:00') + Second(65) = '12:01:05' Time('12:00:00') - Second(65) = '11:58:55' |
Sign | Sign(-10) = -1 Sign(0) = 0 Sign(10) = 1 |
Substract | 2.41 - 10 = -7.59 Date('2016-03-03') - Day(5) = '2016-02-27' Time('15.10.30') - Hour(5) = '10:10:30' Timestamp('2018-12-21-15.10.30.123') - Hour(5) = '2018-12-21-10.10.30.123' |
Substr | Equivalent to: Substring |
Substring | Substring('Hello World', 1, 5) = 'Hello' Substring('Hello World', 7) = 'World' Substring('Hello World', 7, 999) = 'World' Substring('Hello World', 999) = '' |
Time | Time('12:00:00') = '12:00:00' Time('12:00:00') > '11:59:59' Time(Timestamp('2018-12-05-15.30.00.123')) = '15.30.00' |
Timestamp | Timestamp('2018-12-05 12:15:25') = Date('2018-12-05') + Time('12:15:25') |
Trim | Trim(' It works! ') = 'It works!' Trim('***It works!###', '*') = 'It works!###' Trim('123It works!789', '0123456789') = 'It works!' |
Upper | Upper('Hello World') = 'HELLO WORLD' |
Year | Year(Date('2018-12-05')) = 2018 Date('2018-12-05') + Year(1) = '2019-12-05' Date('2018-12-05') - Year(1) = '2017-12-05' |